Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Loading a result set into a temp-table

Enhancements implemented through changes to the RUN STORED-PROC statement allow you to retrieve a result set from a foreign data source and load the result set, for which a temp-table handle is defined, into its own temp-table. The LOAD-RESULT-INTO function enables data retrieved to be loaded into temp-tables where the data can then be manipulated, employing all characteristics inherent to temp-tables. The capability to load result sets into temp-tables is not limited by the parsing requirements associated with the proc-text-buffer nor the database dependencies associated with views.

Temp-tables can provide data management capabilities associated with the 4GL directly to the result sets of a stored procedure, but completely independent of the foreign data source from which it was populated and/or derived. Temporary tables are effectively database tables in which Progress stores data temporarily. Because temp-tables have the same support features that actual OpenEdge databases use, you can take advantage of almost all the OpenEdge database features that do not require data persistence and multi-user access. For example, you can define indexes for fields in the temp-table. For more information about temp-tables, see OpenEdge Development: Progress 4GL Handbook .

Example 3–9 introduces how to use the RUN STORED-PROC statement with the LOAD-RESULT-INTO phrase with a single dynamic temp-table. It highlights the coding techniques discussed in the "Run Stored-Proc statement with send-sql-statement option" section and introduces the dynamic temp-table topic further discussed in this section.

DEFINE VARIABLE tt1 AS HANDLE. DEFINE VARIABLE q AS HANDLE. DEFINE VARIABLE bh AS HANDLE. DEFINE VARIABLE bBrowse AS HANDLE NO-UNDO. DEFINE FRAME BrowseFrame WITH SIZE 80 BY 10. CREATE TEMP-TABLE tt1. RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO tt1 (“SELECT * from CUSTOMER”). bh = tt1:DEFAULT-BUFFER-HANDLE. CREATE QUERY q. q:SET-BUFFERS (bh). DISPLAY tt1:name. q:QUERY-PREPARE(“for each” + tt1:name). q:QUERY-OPEN. CREATE BROWSE hBrowse      ASSIGN ROW = 1 COL = 1      WIDTH = 79 DOWN = 10      FRAME = FRAME BrowseFrame:HANDLE      QUERY = q      SENSITIVE = YES      SEPARATORS = YES      ROW-MARKERS = NO      VISIBLE = YES. hBrowse:ADD-COLUMNS-FROM(bh). ENABLE ALL WITH FRAME BrowseFrame. WAIT-FOR CLOSE OF CURRENT-WINDOW.

Example 3–9: Using the RUN STORED-PROC statement with LOAD-RESULT-INTO phrase with a a single dynamic temp-table

Getting started

If you are using static temp-tables, you must define the temp-table layout in your program to accommodate a specific stored procedure result set before attempting to populate these tables. Once this prerequisite task is done, however, temp-tables can also be automatically populated, offering a potential performance gain in most instances.

Unlike the proc-text-buffer technique, you do not have to parse the strings from the proc-text-buffer pseudo table buffer where each row is a character string. Similarly, you do not need to perform any administration to maintain views in the foreign data source or their definitions in the schema holder. For more details about planning your temp-table layout, see the "Creating a temp-table layout plan" section.

Employing additional enhancements

The temp-table technique offers even greater programming benefits as it extends both send-sql-statement options and stored procedures through the result processing techniques previously described in this chapter. For example, by mapping the PROGRESS_RECID to the ROWID field in temp- tables, you can easily support KEY definitions required by the Progress DataSet (commonly referred to as a ProDataSets) to ensure that your data mappings between the foreign data source and the temp-table are accurate. Accurate data mappings are essential for sending data back from the temp-table to the data source. Non-ROWID key definitions can also be described with a unique key.

ProDataSets functionality is based on one or more temp-tables that share and extend basic temp-table functionality. For more information about ROWID field and using the send-sql-statement with the LOAD-RESULT-INTO option, see the "ROWID support" section. For in depth discussion of temp-tables and more information about ProDataSets, see OpenEdge Development: ProDataSets .

Table 3–3 highlights additional language elements you can use with the stored procedure and the send-sql statement language to use ROWID.

Table 3–3: Returning result sets and loading the data into temp-tables  

Progress 4GL	Description
RUN STORED–PROCEDURE statement	Executes the stored procedure or send-sql- statement option and tells Progress that the stored procedure has ended.
LOAD-RESULT-INTO phrase	Allows data from a result set that is returned for a foreign data source either through a stored procedure or a send-sql- statement option to be put into one or more temp-tables. Static, unprepared dynamic, and prepared dynamic temp-tables are supported. Only one result set can be returned when when using the send-sql-statement option. Note: When using SQL statement(s) through a send-sql-statement option or stored procedure to load result sets into temp-tables, RUN STORED-PROC carries an implicit Run CLOSE-PROC statement. (The stored procedure’s output parameters are available after the RUN STORED-PROC executes and closes the procedure.)
PROC-STATUS phrase	Reads the return value (optional).

Creating a temp-table layout plan

You must define the temp-table layout in your application program to accommodate specific result sets before you attempt to populate the temp-tables with data. If a SQL statement retrieves more than one result set, you must define multiple temp-tables to be able to retrieve all the data. Therefore, the success of this approach depends to a large extent on your:

Understanding of the specific data your foreign data source is providing you through a given stored procedure.
Ability to correctly define temp-tables.

The following types of temp-tables can support result sets:

Static — A temp-table whose schema is defined at compile time.
Dynamic — A temp-table whose schema is defined at run time. There are two types of dynamic temp-tables: dynamic-prepared and dynamic-unprepared.

Keep in mind that you can pass handles of temp-tables that contain a mixed array. A mixed array is one in which some of the temp-table handle elements can be static while others can be dynamic.

Table 3–4 identifies the temp-table options for which you can plan and the requirements you must fulfill for each option.

Using a temp-table handle with an unprepared dynamic temp-table

When a temp-table handle points to an unprepared dynamic temp-table, the ORACLE Server DataServer defines the temp-table schema in the form of the result sets record structure which is passed back to the DataServer from the foreign data source. The data types defined for the temp-table schema are determined based on the default data type mapping that exists between the SQL data type and its equivalent Progress default data type. Once the temp-table schema is dynamically established by the DataServer, the result set begins to populate it.

Recognize that there is the possibility of a small performance price to be paid when you build dynamic temp-tables. However, considering the database independence that this technique affords over building static temp-tables, you might consider the price of dynamically built temp-tables to be a small, reasonable one.

Table 3–4: Options to plan the temp-table layout for result sets 

To return a result set to this type of temp-table...	Then the layout definition is...
Static Dynamic-prepared state	Defined by you; you must base the layout on the expected fields to be returned and each of these fields’ data types so that the first field defined in the temp-table corresponds to the first column of the result set. This column matching and data type matching must be repeated successfully for each temp-table and its corresponding result set.
Dynamic - unprepared state	Not defined by you; the schema of the temp-table is based on the result-set schema and a mapping of default Progress data types for each SQL type. For more information, see the "Details about a Dynamic temp-table in an unprepared state" section.

Note: Once the data is loaded into the temp-table, any updates made to the records in the temp-table are not propagated back to the foreign database. Result sets are available through temp-tables for the purpose of obtaining a snapshot of the data. For example, you can use this technique to populate a browser from a temp-table. You must re-read the record using the proper lock mechanism to actually update the record.

Details about a Dynamic temp-table in an unprepared state

A dynamic temp-table is considered to be in an unprepared state after the first definitional method is called until the temp-table is prepared. If a clear dynamic temp-table handle is passed, the DataServer populates the temp-table schema based on the result-set schema and prepares the temp-table. A clear dynamic temp-table is a table that is in an unprepared state where definitional methods have not yet been called. The DataServer then executes the temp-table handle: ADD-NEW-FIELD (field name, data type) internally for each one of the columns of the result set. If a column in the result set from the foreign schema does not have a name (for example, an array element field or a SQL-derived field), the DataServer assigns a name to it based on the column position on the row.

For example, if you run the following statement, then the temp-table contains columns:
column 1, cust_num, name and column4:

/* */ SELECT “mytag”, cust_num, name, (salary * 0.10) FROM <anytablename>

The data type associated with each column follows the mapping rules that exist between Progress and the foreign data source’s data types. For more information about data types and default mapping, see Chapter 2, " Initial Programming Considerations."

Note: Since a stored procedure can return multiple result sets, the DataServer prepares the temp-table as “result<n>” where <n> is the result-set sequence number that corresponds to its element position in the temp table handle array, starting with 1. Therefore, if there are 3 result sets and 3 clear dynamic temp-tables are passed, the temp-tables are called result1, result2, and result3.

Note the following error conditions as they specifically apply to a dynamic temp-table:

If an error occurs during the schema population or during the prepare of a temp-table, the DataServer raises an error condition.
The dynamic temp-table must be either already prepared or clear with no defined fields in it. If fields have already been added to a dynamic temp-table before the RUN STORED-PROC statement is executed and the temp-table is not prepared, the DataSever raises an error condition due to the invalid state of the temp-table.

Details about a Dynamic temp-table in a prepared state

Example 3–10 shows multiple dynamic temp-tables in a prepared state. It is based on the stored procedure code presented in Example 3–8 which shows the basics of executing a call to a stored procedure that returns multiple result sets using the functionality that the LOAD-RESULT-INTO phrase supports. Each result set will be loaded into a separate temp-table.

/* Second part of this example - Progress 4GL code*/ DEFINE VAR tt1 AS HANDLE. DEFINE VAR tt2 AS HANDLE. DEFINE VAR tt-array AS HANDLE EXTENT 2. CREATE TEMP-TABLE tt1. tt1:ADD-NEW-FIELD(“cust-num”, “integer”). tt1:ADD-NEW-FIELD(“name”, “character”). tt1:TEMP-TABLE-PREPARE (“custx1”). CREATE TEMP-TABLE tt2. tt2:ADD-NEW-FIELD(“order-num”, “integer”). tt2:ADD-NEW-FIELD(“order-date”, “date”). tt2:TEMP-TABLE-PREPARE (“ordx1”). Assign tt-array[1]=tt1        tt-array[2]=tt2. RUN STORED-PROC pcustorder LOAD-RESULT-INTO tt-array (INPUT 10, OUTPUT 0, OUTPUT 0).

Example 3–10: Multiple dynamic temp-tables shown in a prepared state

Additional temp-table examples

This section presents more examples that show various techniques to code temp-tables.

Example 3–11 shows the basics of executing a call to a stored procedure using the functionality that the LOAD-RESULT-INTO phrase supports. Note that the code works with the stored procedure in Example 3–8 comparable to the way you can see it works with the 4GL code.

/*Calling a stored procedure, using a static temporary table*/ DEFINE TEMP-TABLE tt1 FIELD cust-num AS INTEGER FIELD name AS CHARACTER. DEFINE TEMP-TABLE tt2. FIELD order-num AS INTEGER FIELD order-date AS DATE. DEFINE VAR tt-array AS HANDLE EXTENT 2. ASSIGN tt-array[1] = TEMP-TABLE tt1 HANDLE      tt-array[2] = TEMP-TABLE tt2:HANDLE. RUN STORED-PROC pcustorder LOAD-RESULT-INTO tt-array (INPUT 10, OUTPUT 0, OUTPUT 0).

Example 3–11: Basic technique to execute a stored procedure call using the LOAD-RESULT-INTO phrase

Example 3–12 shows the basics of using an existing dynamic temp-table without the TEMP-TABLE-PREPARE () method. In this instance, the send-sql-statement option is used rather than a predefined stored proc. In contrast, the third example code that appears later in this section shows the same approach, but explicitly defines the existing dynamic temp-table with the TEMP-TABLE-PREPARE() method.

/* Calling a stored procedure, using an existing temp-table without temp table prepare*/ DEF VAR ttHndl AS HANDLE. CREATE TEMP-TABLE ttHndl. RUN STORED-PROC send-sql-statement  LOAD-RESULT-INTO ttHndl  (“select * from customer”).

Example 3–12: Using an existing temp-table without the TEMP-TABLE-PREPARE ( ) method

Example 3–13 shows the basics of using an existing dynamic temp-table with the TEMP-TABLE-PREPARE() method.

/* Calling a stored procedure, using an existing temp-table with temp table prepare*/ DEF VAR ttHndl AS HANDLE CREATE TEMP-TABLE tt1Hndl.      ttHndl:ADD-NEW-FIELD(“custNum”,”integer”).      ttHndl:ADD-NEW-FIELD(“name”,”char”).      ttHndl:TEMP-TABLE-PREPARE(“ordX”). RUN STORED-PROC send-sql-statement  LOAD-RESULT-INTO ttHndl (“select  custNum,name from myDB.customer”).

Example 3–13: Using an existing temp-table with the TEMP-TABLE-PREPARE ( ) method

Note these points as they relate the Example 3–13:

As a prerequisite for creating the code shown in the previous example, the developer would need to define the schema for the table.
Once the temp-table schema begins preparation from the clear state, the temp-table must be defined to the exact specifications for the result sets as generated by the RUN STORED-PROC statement or send-sql-statement option. Otherwise, the RUN STORED-PROC will end in failure. Also, note the TEMP-TABLE-PREPARE must be called at the completion of the temp-table definition associated with the stored procedure results.

Example 3–14 shows the syntax for the stored procedure with the LOAD-RESULT-INTO phrase with a single static temp-table and the send-sql-statement option.

/* Calling a stored procedure that uses the LOAD-RESULT-INTO phrase with a single temp table and the send-sql-statement option*/ define variable tt-handle as handle. define TEMP-TABLE tt1 Field f1 as int Field f2 as char. tt-handle=temp-table tt1:handle. RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO tt-handle (“Select cust_num,name from customer”).

Example 3–14: Calling a stored procedure that uses the LOAD-RESULT-INTO phrase with a single temp table and the send-sql-statement option

Example 3–15 shows the use of a PROC-STATUS phrase. The PROC-STATUS phrase must be defined as part of the RUN STORED-PROC statement because of the implicit CLOSE STORED-PROC that is associated with the LOAD-RESULT-INTO phrase.

/* Example of the implicit close stored-proc and use of LOAD-RESULT-INTO */ DEFINE VAR stat AS integer. DEFINE VARIABLE ttHndl AS HANDLE. CREATE temp-table ttHndl. RUN STORED-PROCEDURE pcust LOAD-RESULT-INTO ttHndl stat=PROC-STATUS (20, output 0, output 0). DISPLAY stat.

Example 3–15: Using the PROC-STATUS phrase

In Example 3–15, note that the PROC-STATUS phrase does not need a PROC-HANDLE phrase because it is retrieved using the RUN STORED-PROC statement and not after this statement’s execution as it typically is used.

Example 3–16 is a two-part example that shows the basics of executing a call to a stored function that returns a result set using a cursor and the functionality that the LOAD-RESULT-INTO phrase supports. The result set will be loaded into a temp-table.

/* First part of the procedure - Stored-function code*/ create or REPLACE function myfunc_2 (num in number) return cv_types. GenericCurType is c1 cv_types.GenericCurType; begin OPEN c1 FOR    select *      from customer where cust_num>=num; return c1; end; /

/* Second part of the procedure - Progress 4GL code*/ DEFINE VAR tth as HANDLE. CREATE TEMP-TABLE tth. RUN STORED-PROC myfunc_2 LOAD-RESULT-INTO tth (OUTPUT ?, INPUT 10).

Example 3–16: Executing a call to a stored function that returns a result set using the LOAD-RESULT-INTO phrase.